import sqlite3
from datetime import datetime, timedelta

DATABASE = 'alpha_score.db'


def init_db():
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()

    # 创建积分表
    c.execute('''
        CREATE TABLE IF NOT EXISTS tb_alpha_score (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            account TEXT NOT NULL,
            date TEXT NOT NULL,
            balance_score REAL NOT NULL,
            volume_score REAL NOT NULL,
            consume_score REAL NOT NULL,
            remark TEXT
        )
    ''')

    # 创建索引以提高查询性能
    c.execute('CREATE INDEX IF NOT EXISTS idx_date ON tb_alpha_score(date)')

    conn.commit()
    conn.close()


def add_score_record(account, date, balance_score, volume_score, consume_score, remark):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('''
        INSERT INTO tb_alpha_score 
        (account, date, balance_score, volume_score, consume_score, remark) 
        VALUES (?, ?, ?, ?, ?, ?)
    ''', (account, date, balance_score, volume_score, consume_score, remark))
    conn.commit()
    conn.close()


def update_score_record(record_id, account, date, balance_score, volume_score, consume_score, remark):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('''
        UPDATE tb_alpha_score 
        SET account=?, date=?, balance_score=?, volume_score=?, consume_score=?, remark=?
        WHERE id=?
    ''', (account, date, balance_score, volume_score, consume_score, remark, record_id))
    conn.commit()
    conn.close()


def delete_score_record(record_id):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('DELETE FROM tb_alpha_score WHERE id=?', (record_id,))
    conn.commit()
    conn.close()


def get_record_by_id(record_id):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('SELECT * FROM tb_alpha_score WHERE id=?', (record_id,))
    record = c.fetchone()
    conn.close()
    return record


def get_recent_records(date_limit):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('''
        SELECT * FROM tb_alpha_score 
        WHERE date >= ? 
        ORDER BY date DESC, id DESC
    ''', (date_limit,))
    records = c.fetchall()
    conn.close()
    return records


def get_records_by_date(date):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()
    c.execute('''
        SELECT * FROM tb_alpha_score 
        WHERE date = ? 
        ORDER BY id DESC
    ''', (date,))
    records = c.fetchall()
    conn.close()
    return records


def calculate_summary(date_limit):
    conn = sqlite3.connect(DATABASE)
    c = conn.cursor()

    # 计算各项积分总和
    c.execute('''
        SELECT 
            SUM(balance_score) AS total_balance,
            SUM(volume_score) AS total_volume,
            SUM(consume_score) AS total_consume
        FROM tb_alpha_score 
        WHERE date >= ?
    ''', (date_limit,))

    result = c.fetchone()
    conn.close()

    if result:
        total_balance = result[0] or 0
        total_volume = result[1] or 0
        total_consume = result[2] or 0
        total_score = total_balance + total_volume - total_consume

        return {
            'total_balance': total_balance,
            'total_volume': total_volume,
            'total_consume': total_consume,
            'total_score': total_score
        }

    return {
        'total_balance': 0,
        'total_volume': 0,
        'total_consume': 0,
        'total_score': 0
    }